package com.madev.isellcrazyitems.handler;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;

import com.madev.isellcrazyitems.entity.Product;
import com.madev.isellcrazyitems.entity.Reservation;
import com.madev.isellcrazyitems.entity.Sales;
import com.madev.isellcrazyitems.entity.SalesOrder;
import com.madev.isellcrazyitems.entity.Statement;
import com.madev.isellcrazyitems.entity.User;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;


public class DatabaseHandler extends SQLiteOpenHelper {

	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	public static String DATABASENAME = "MADEVDatabase";
	
	public static String tableSales = "Sales";
	public static String colSalesID = "SalesID";
	public static String colSalesCustomerID = "SalesCustomerID";
	public static String colSalesPurchaseDate = "SalesPurchaseDate";
	
	public static String tableSalesOrder = "SalesOrder";
	public static String colSalesOrderID = "SalesOrderID";
	public static String colSalesOrderProductID = "SalesOrderProductID";
	public static String colSalesOrderQuantity = "SalesOrderQuantity";
	
	private static final String TABLE_USER = "User";
	private static final String colUserId = "userId";
	private static final String colUserFullName = "userFullName";
	private static final String colUserEmail = "userEmail";
	private static final String colUserPassword = "userPassword";
	private static final String colUserAddress = "userAddress";
	private static final String colUserDob = "userDob";
	private static final String colCreateDate = "createdDate";
	private static final String colUpdateDate = "updateDate";
	private static final String colUserType ="userType";
	
	public static String PRODUCTTABLE = "Product";
	public static String colProductId = "id";
	public static String _colProductid = "productIdNo";
	public static String colProductName = "productName";
	public static String colProductPrice = "productPrice";
	public static String colProductStock = "productStock";
	public static String colProductImagePath = "productImagePath";
	private ArrayList<Product> cartList = new ArrayList<Product>();
	
	public static String SYNCTABLE = "SYNCTABLE";
	public static String colStatementId = "id";
	public static String _colStatementId = "statementIdNo";
	public static String colStatement = "statement";
	private ArrayList<Statement> stList = new ArrayList<Statement>();
	
	private static final String TABLE_LOGIN = "Login";

	private static final String TABLE_RESERVATION = "Reservation";

	private static final String colReservationId = "reservationId";
	private static final String colProductIdNo = "productIdNo";
	private static final String colReserveCreateDate = "reserveCreateDate";
	private static final String colQuantity = "quantity";
	
	SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
	SimpleDateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	SimpleDateFormat pmFormat = new SimpleDateFormat("MM/dd/yyyy  hh:mm aa");
	SimpleDateFormat stringFormat = new SimpleDateFormat("dd MMMM yyyy");
	
	Calendar c = Calendar.getInstance();	
	Date d = c.getTime();
	String todayDate = dateFormat.format(d);
	String todayDateTime = dateTimeFormat.format(d);
	String todayPMDateTime = pmFormat.format(d);
	
	Context context;

	public DatabaseHandler(Context context) {
		super(context, DATABASENAME, null, 33);
		this.context = context;
	}

	public void createSales(int customerID, String purchaseDate) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues contentValues = new ContentValues();

		contentValues.put(colSalesCustomerID, customerID);
		contentValues.put(colSalesPurchaseDate, purchaseDate);
		db.insert(tableSales, null, contentValues);
	
		db.close();

	}

	public void addProdExample(SQLiteDatabase db){

		ContentValues contentValues = new ContentValues();
		contentValues.put("productIdNo", 1);
		contentValues.put("productName", "Shikamaru");
		contentValues.put("productPrice", "3.50");
		contentValues.put("productStock", "33");
		contentValues.put("productImagePath", "http://i209.photobucket.com/albums/bb271/SleepyDreamGirl/ShikamaruFront.jpg");

		ContentValues contentValues2 = new ContentValues();
		contentValues2.put("productIdNo", 2);
		contentValues2.put("productName", "Zoro");
		contentValues2.put("productPrice", "4.20");
		contentValues2.put("productStock", "40");
		contentValues2.put("productImagePath", "http://i209.photobucket.com/albums/bb271/SleepyDreamGirl/ZoroMafiaFront.jpg");

		ContentValues contentValues3 = new ContentValues();
		contentValues3.put("productIdNo", 3);
		contentValues3.put("productName", "Going Merry");
		contentValues3.put("productPrice", "55.20");
		contentValues3.put("productStock", "2");
		contentValues3.put("productImagePath", "http://i209.photobucket.com/albums/bb271/SleepyDreamGirl/GoingMerry.jpg");
		
		ContentValues contentValues4 = new ContentValues();
		contentValues4.put("productIdNo", 4);
		contentValues4.put("productName", "Nico Robin");
		contentValues4.put("productPrice", "2.35");
		contentValues4.put("productStock", "39");
		contentValues4.put("productImagePath", "http://i209.photobucket.com/albums/bb271/SleepyDreamGirl/RobinMafiaFront.jpg");
		
		ContentValues contentValues5 = new ContentValues();
		contentValues5.put("productIdNo", 5);
		contentValues5.put("productName", "Abasalom");
		contentValues5.put("productPrice", "6.20");
		contentValues5.put("productStock", "34");
		contentValues5.put("productImagePath", "http://i209.photobucket.com/albums/bb271/SleepyDreamGirl/AbsalomFront.jpg");
		
		ContentValues contentValues6 = new ContentValues();
		contentValues6.put("productIdNo", 6);
		contentValues6.put("productName", "Luffy");
		contentValues6.put("productPrice", "3.70");
		contentValues6.put("productStock", "0");
		contentValues6.put("productImagePath", "http://i209.photobucket.com/albums/bb271/SleepyDreamGirl/LuffyMafiaFront.jpg");
		
		db.insert(PRODUCTTABLE, null, contentValues);
		db.insert(PRODUCTTABLE, null, contentValues2);
		db.insert(PRODUCTTABLE, null, contentValues3);
		db.insert(PRODUCTTABLE, null, contentValues4);
		db.insert(PRODUCTTABLE, null, contentValues5);
		db.insert(PRODUCTTABLE, null, contentValues6);
		
		
		//db.close();
	}
	public void deleteSales(String condition) {
		try {
			SQLiteDatabase db = this.getWritableDatabase();
			String query = "DELETE from " + tableSales;
			
			if (condition != null){
				query += condition;
			}
			
			db.execSQL(query);
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public Sales retrieveSales(String condition) {
		Sales sales = new Sales();
		SQLiteDatabase db = this.getWritableDatabase();
		
		String query = "SELECT * from " + tableSales;
		
		if (condition != null){
			query += condition;
		}
		
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.getCount() != 0) {
			if (cursor.moveToFirst()) {
				do {
					
					sales.setSalesID(cursor.getInt(cursor
							.getColumnIndex(colSalesID)));

					sales.setSalesCustomerID(cursor.getInt(cursor
							.getColumnIndex(colSalesCustomerID)));
					
					try {
						sales.setSalesPurchaseDate(stringToDate(cursor.getString(cursor
								.getColumnIndex(colSalesPurchaseDate))));
					} catch (ParseException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
				} while (cursor.moveToNext());
			}
		}
		cursor.close();
		db.close();
		return sales;
	}
	
	public Sales retrieveSales(int customerID) {
		Sales sales = new Sales();
		SQLiteDatabase db = this.getWritableDatabase();
		
		String query = "SELECT * from " + tableSales;
		
		if (customerID != 0){
			query += " WHERE " + colSalesCustomerID + " = " + customerID;
		}
		
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.getCount() != 0) {
			if (cursor.moveToFirst()) {
				do {
					
					sales.setSalesID(cursor.getInt(cursor
							.getColumnIndex(colSalesID)));

					sales.setSalesCustomerID(cursor.getInt(cursor
							.getColumnIndex(colSalesCustomerID)));
					
					try {
						sales.setSalesPurchaseDate(stringToDate(cursor.getString(cursor
								.getColumnIndex(colSalesPurchaseDate))));
					} catch (ParseException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
				} while (cursor.moveToNext());
			}
		}
		cursor.close();
		db.close();
		return sales;
	}

	public void createSalesOrder(int id, int itemID, int quantity) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		contentValues.put(colSalesOrderID, id);
		contentValues.put(colSalesOrderProductID, itemID);
		contentValues.put(colSalesOrderQuantity, quantity);
		db.insert(tableSalesOrder, null, contentValues);
		db.close();

	}
	
	public void deleteSalesOrder(String condition) {
		try {
			SQLiteDatabase db = this.getWritableDatabase();
			String query = "DELETE from " + tableSalesOrder;
			
			if (condition != null){
				query += condition;
			}
			
			db.execSQL(query);
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public SalesOrder retrieveSalesOrder(String condition) {
		SalesOrder salesOrder = new SalesOrder();
		SQLiteDatabase db = this.getWritableDatabase();
		
		String query = "SELECT * from " + tableSalesOrder;
		
		if (condition != null){
			query += condition;
		}
		
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.getCount() != 0) {
			if (cursor.moveToFirst()) {
				do {
					
					salesOrder.setSalesOrderID(cursor.getInt(cursor
							.getColumnIndex(colSalesOrderID)));

					salesOrder.setSalesOrderProductID(cursor.getInt(cursor
							.getColumnIndex(colSalesOrderProductID)));
					
					salesOrder.setSalesQuantity(cursor.getInt(cursor
							.getColumnIndex(colSalesOrderQuantity)));
					
				} while (cursor.moveToNext());
			}
		}
		cursor.close();
		db.close();
		return salesOrder;
	}
	
	public SalesOrder retrieveSalesOrder(int salesID) {
		SalesOrder salesOrder = new SalesOrder();
		SQLiteDatabase db = this.getWritableDatabase();
		
		String query = "SELECT * from " + tableSalesOrder;
		
		if (salesID != 0){
			query += " WHERE " + colSalesOrderID + " = " + salesID;
		}
		
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.getCount() != 0) {
			if (cursor.moveToFirst()) {
				do {
					
					salesOrder.setSalesOrderID(cursor.getInt(cursor
							.getColumnIndex(colSalesOrderID)));

					salesOrder.setSalesOrderProductID(cursor.getInt(cursor
							.getColumnIndex(colSalesOrderProductID)));
					
					salesOrder.setSalesQuantity(cursor.getInt(cursor
							.getColumnIndex(colSalesOrderQuantity)));
					
				} while (cursor.moveToNext());
			}
		}
		cursor.close();
		db.close();
		return salesOrder;
	}

	
	@Override
	public void onCreate(SQLiteDatabase db) {

		//create Sales
		db.execSQL("CREATE TABLE if not exists " + tableSales + "(" + colSalesID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ colSalesCustomerID
				+ " INTEGER,"
				+ colSalesPurchaseDate
				+ " TEXT)");
		
		//create SalesOrder
		db.execSQL("CREATE TABLE if not exists " + tableSalesOrder + "(" + colSalesOrderID + " INTEGER,"
				+ colSalesOrderProductID
				+ " INTEGER,"
				+ colSalesOrderQuantity
				+ " INTEGER,"
				+ "PRIMARY KEY(" + colSalesOrderID + ", " + colSalesOrderProductID + "))");
		
		//create User
		String CREATE_LOGIN_TABLE = "CREATE TABLE if not exists " + TABLE_USER + "("
				+ colUserId + " INTEGER PRIMARY KEY AUTOINCREMENT ," 
				+ colUserFullName + " TEXT,"
				+ colUserEmail + " TEXT UNIQUE,"
				+ colUserPassword + " TEXT,"
				+ colUserAddress + " TEXT,"
				+ colUserDob + " DATE,"
				+ colUpdateDate + " TEXT,"
				+ colCreateDate + " TEXT," 
				+ colUserType + " TEXT" 
				+ ")";

		db.execSQL(CREATE_LOGIN_TABLE);
		addUserExample(db);
		String INSERT_ADMIN = "";
		
		//create Product
		db.execSQL("CREATE TABLE if not exists " + PRODUCTTABLE + " (id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "productIdNo"
				+ " TEXT,"
				+ "productName"
				+ " TEXT,"
				+ "productPrice" 
				+ " TEXT," 
				+ "productStock" 
				+ " TEXT,"
				+ "productImagePath"+ " TEXT)");
		
		addProdExample(db);

		
		// create Sync
		
		db.execSQL("CREATE TABLE if not exists SYNCTABLE(id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "statementIdNo" + " TEXT ," + "statement" + " TEXT)");
		
		//create Login
		String CREATE_TABLE_LOGIN = "CREATE TABLE if not exists " + TABLE_LOGIN + "("
				+ colUserId + " INTEGER PRIMARY KEY AUTOINCREMENT ," 
				+ colUserFullName + " TEXT,"
				+ colUserEmail + " TEXT UNIQUE,"
				+ colUserPassword + " TEXT,"
				+ colUserAddress + " TEXT,"
				+ colUserDob + " DATE,"
				+ colUpdateDate + " TEXT,"
				+ colCreateDate + " TEXT," 
				+ colUserType + " TEXT" 
				+ ")";
		db.execSQL(CREATE_TABLE_LOGIN);
		
		String CREATE_RESERVATION_TABLE = "CREATE TABLE if not exists " + TABLE_RESERVATION + "("
				+ colReservationId + " INTEGER PRIMARY KEY AUTOINCREMENT ," 
				+ colUserEmail + " TEXT,"
				+ colProductIdNo + " TEXT,"		
				+ colQuantity + " INTEGER,"
				+ colReserveCreateDate + " TEXT"
				+ ")";
		db.execSQL(CREATE_RESERVATION_TABLE);
		addReservationExample(db);
		
	}
	
	public void addUserExample(SQLiteDatabase db){
		User admin = new User();
		admin.setUserFullName("admin");
		admin.setUserEmail("admin");
		admin.setUserPassword("admin");
		admin.setUserAddress("admin");
		admin.setUserDob("0000-00-00");
		admin.setUserType("admin");
	
		
		User customer = new User();
		customer.setUserFullName("Customer Tan");
		customer.setUserEmail("customer");
		customer.setUserPassword("customer");
		customer.setUserAddress("customer");
		customer.setUserDob("1993-01-25");
		customer.setUserType("customer");
		
		//SQLiteDatabase db = this.getWritableDatabase();
		boolean success = false;

		ContentValues values = new ContentValues();
		values.put(colUserFullName, admin.getUserFullName()); // Email
		values.put(colUserEmail, admin.getUserEmail()); // Email
		values.put(colUserPassword, admin.getUserPassword()); // 
		values.put(colUserAddress, admin.getUserAddress());
		values.put(colUserDob, admin.getUserDob());		
		values.put(colCreateDate, todayPMDateTime);
		values.put(colUserType, admin.getUserType());
		// Inserting Row
		if(db.insert(TABLE_USER, null, values) > 0)
			{
				success = true;
			}
		values.put(colUserFullName, customer.getUserFullName()); // Email
		values.put(colUserEmail, customer.getUserEmail()); // Email
		values.put(colUserPassword, customer.getUserPassword()); // 
		values.put(colUserAddress, customer.getUserAddress());
		values.put(colUserDob, customer.getUserDob());		
		values.put(colCreateDate, todayPMDateTime);
		values.put(colUserType, customer.getUserType());
		// Inserting Row
		if(db.insert(TABLE_USER, null, values) > 0)
			{
				success = true;
			}
		//db.close(); // Closing database connection

	}
	
	public void delete(){
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("drop table if exists Sales");
		db.execSQL("drop table if exists SalesOrder");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS" + tableSales);
		db.execSQL("DROP TABLE IF EXISTS" + tableSalesOrder);
		db.execSQL("DROP TABLE IF EXISTS" + TABLE_USER);
		db.execSQL("DROP TABLE IF EXISTS" + PRODUCTTABLE);
		db.execSQL("DROP TABLE IF EXISTS" + SYNCTABLE);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_LOGIN);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_RESERVATION);
		onCreate(db);
	}

	@SuppressLint("SimpleDateFormat")
	public String dateToString(Date date) {
	    return sdf.format(date);
	}
	
	@SuppressLint("SimpleDateFormat")
	public Date stringToDate(String date) throws ParseException{
		return sdf.parse(date);
	}
	
	/**
	 * Storing user details in database
	 * */
	public boolean addUser(User u) {
		SQLiteDatabase db = this.getWritableDatabase();
		boolean success = false;

		ContentValues values = new ContentValues();
		values.put(colUserFullName, u.getUserFullName()); // Email
		values.put(colUserEmail, u.getUserEmail()); // Email
		values.put(colUserPassword, u.getUserPassword()); // 
		values.put(colUserAddress, u.getUserAddress());
		values.put(colUserDob, u.getUserDob());		
		values.put(colCreateDate, todayPMDateTime);
		values.put(colUserType, u.getUserType());
		// Inserting Row
		if(db.insert(TABLE_USER, null, values) > 0)
			{
				success = true;
			}
		db.close(); // Closing database connection
		return success;
	}
	
	
	public boolean updateUser(User u) {
		SQLiteDatabase db = this.getWritableDatabase();
		boolean success = false;
		
		ContentValues values = new ContentValues();
		values.put(colUserFullName, u.getUserFullName()); // Email
		values.put(colUserEmail, u.getUserEmail()); // Email
		values.put(colUserPassword, u.getUserPassword()); // 
		values.put(colUserAddress, u.getUserAddress());
		values.put(colUserDob, u.getUserDob());		
		values.put(colUpdateDate, todayPMDateTime);
		
		String where = colUserId + " = " + u.getUserId();
		
		// Inserting Row
		if(db.update(TABLE_USER, values, where, null) > 0)
			{
				success = true;
			}
		db.close(); // Closing database connection
		return success;
	}
	
	public boolean setUserAsAdminByEmail(String email) {
		SQLiteDatabase db = this.getWritableDatabase();
		boolean success = false;
		
		ContentValues values = new ContentValues();
		values.put(colUserType, "admin");
		
		String where = colUserEmail + " = " + email;
		
		// Inserting Row
		if(db.update(TABLE_USER, values, where, null) > 0)
			{
				success = true;
			}
		db.close(); // Closing database connection
		return success;
	}
	

	
	
	/**
	 * Getting user data from database
	 * */
	public User getUserByUserEmail(String userEmail){
		//HashMap<String,String> user = new HashMap<String,String>();
		String selectQuery = "SELECT  * FROM " + TABLE_USER + " WHERE userEmail= '" + userEmail + "'";
		User u = new User();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        if(cursor.getCount() > 0){
        	//user.put("userId", cursor.getString(1));
        	u.setUserId(Integer.parseInt(cursor.getString(0)));
       
        	//user.put("userFullName", cursor.getString(2));
        	u.setUserFullName(cursor.getString(1));
        	
        	//user.put("userEmail", cursor.getString(3));
        	u.setUserEmail(cursor.getString(2));
        	
        	//user.put("userPassword", cursor.getString(4));
        	
        	//user.put("userAddress", cursor.getString(5));
        	u.setUserAddress(cursor.getString(4));
        	
        	//user.put("userDob", cursor.getString(6));
        	u.setUserDob(cursor.getString(5));
        	
        	//user.put("createDate", cursor.getString(7));
        	u.setUpdateDate(cursor.getString(6));
        	
        	//user.put("updateDate", cursor.getString(8));
        	u.setCreateDate(cursor.getString(7));
        	
        	u.setUserType(cursor.getString(8));
        }
        cursor.close();

        db.close();
		// return user
		return u;
	}
	
	public User getUserByEmailAndPassword(String userEmail, String userPassword) {
		String countQuery = "SELECT  * FROM " + TABLE_USER + " WHERE userEmail = '" + userEmail + 
				"' AND userPassword = '" + userPassword + "'";
		Log.d("Query: ", countQuery);
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
        cursor.moveToFirst();
        User u = null;
        
        if(cursor.getCount() > 0){
        	u = new User();
        	u.setUserId(Integer.parseInt(cursor.getString(0)));
        	u.setUserFullName(cursor.getString(1));
        	u.setUserEmail(cursor.getString(2));
        	u.setUserAddress(cursor.getString(4));
        	u.setUserDob(cursor.getString(5));
        	u.setUpdateDate(cursor.getString(6));    	
        	u.setCreateDate(cursor.getString(7));
        	u.setUserType(cursor.getString(8));
        }
		
		db.close();
		cursor.close();
		
		// return row count
		return u;
	}

	public void addProduct(Product productitem) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		contentValues.put("productIdNo", productitem.productID);
		contentValues.put("productName", productitem.productName);
		contentValues.put("productPrice", productitem.productPrice);
		contentValues.put("productStock", productitem.productStock);
		contentValues.put("productImagePath", productitem.productImagePath);

		db.insert(PRODUCTTABLE, null, contentValues);
		db.close();

	}

	public void emptyProduct() {
		try {
			SQLiteDatabase db = this.getWritableDatabase();
			db.execSQL("delete from " + PRODUCTTABLE);
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public ArrayList<Product> getProducts() {

		cartList.clear();

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery("select * from " + PRODUCTTABLE, null);
		if (cursor.getCount() != 0) {
			if (cursor.moveToFirst()) {
				do {
					Product item = new Product();

					item.productID = cursor.getString(cursor
							.getColumnIndex("productIdNo"));

					item.productName = cursor.getString(cursor
							.getColumnIndex("productName"));

					item.productPrice = cursor.getString(cursor
							.getColumnIndex("productPrice"));

					item.productStock = cursor.getString(cursor
							.getColumnIndex("productStock"));

					item.productImagePath = cursor.getString(cursor
							.getColumnIndex("productImagePath"));
					
					
					cartList.add(item);

				} while (cursor.moveToNext());
			}
		}
		cursor.close();
		db.close();
		return cartList;
	}
	
	public Product getProductByProductIdNo(String id) {

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery("select * from " + PRODUCTTABLE + " where productIdNo= " + id, null);
		Product item = new Product();
		if (cursor.getCount() != 0) {
			if (cursor.moveToFirst()) 
			{
		
			item.productID = cursor.getString(cursor
					.getColumnIndex("productIdNo"));

			item.productName = cursor.getString(cursor
					.getColumnIndex("productName"));

			item.productPrice = cursor.getString(cursor
					.getColumnIndex("productPrice"));

			item.productStock = cursor.getString(cursor
					.getColumnIndex("productStock"));

			item.productImagePath = cursor.getString(cursor
					.getColumnIndex("productImagePath"));

			}
		}
		cursor.close();
		db.close();
		return item;
	}

	public ArrayList<Product> getSearched(String type, String term) {

		cartList.clear();

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = null;

		if (type.equals("id")) {
			cursor = db.rawQuery(
					"select * from " + PRODUCTTABLE + " where productIdNo ='" + term
							+ "'", null);
		} else if (type.equals("name")) {
			cursor = db.rawQuery(
					"select * from " + PRODUCTTABLE + " where productName LIKE '"
							+ term + "%'", null);
		} else if (type.equals("stock")) {
			cursor = db.rawQuery(
					"select * from " + PRODUCTTABLE + " where productStock ='" + term
							+ "'", null);
		}

		if (cursor.getCount() != 0) {
			if (cursor.moveToFirst()) {
				do {
					Product item = new Product();

					item.productID = cursor.getString(cursor
							.getColumnIndex("productIdNo"));

					item.productName = cursor.getString(cursor
							.getColumnIndex("productName"));

					item.productPrice = cursor.getString(cursor
							.getColumnIndex("productPrice"));

					item.productStock = cursor.getString(cursor
							.getColumnIndex("productStock"));

					item.productImagePath = cursor.getString(cursor
							.getColumnIndex("productImagePath"));
					
					cartList.add(item);

				} while (cursor.moveToNext());
			}
		}
		cursor.close();
		db.close();
		return cartList;
	}

	// update


	public void removeProduct(String productid) {
		try {
			// SQLiteDatabase db = this.getWritableDatabase();
			// db.execSQL("delete from producttable where productIdNo="
			// + productid);
			// db.close();

			String[] args = { productid };
			getWritableDatabase().delete( PRODUCTTABLE , "productIdNo=?", args);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void updateProduct(Product productList) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		contentValues.put("productName", productList.productName);
		contentValues.put("productStock", productList.productStock);
		contentValues.put("productPrice", productList.productPrice);
		contentValues.put("productImagePath", productList.productImagePath);
		
		db.update(PRODUCTTABLE, contentValues, "productIdNo="
				+ productList.productID, null);

		db.close();
	}
	
	public void updateProductQuantity(int id, int qty) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		contentValues.put("productStock", qty);
		
		db.update(PRODUCTTABLE, contentValues, "productIdNo="
				+ id, null);

		db.close();
	}
	
	public void addStatement(Statement st) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		contentValues.put("statement", st.statement);

		db.insert("SYNCTABLE", null, contentValues);
		db.close();

	}

	public void emptyStatements() {
		try {
			SQLiteDatabase db = this.getWritableDatabase();
			db.execSQL("delete from SYNCTABLE");
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public ArrayList<Statement> getStatements() {

		stList.clear();

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery("select * from SYNCTABLE", null);
		if (cursor.getCount() != 0) {
			if (cursor.moveToFirst()) {
				do {
					Statement st = new Statement();

					st.statementId = cursor.getString(cursor
							.getColumnIndex("statementIdNo"));

					st.statement = cursor.getString(cursor
							.getColumnIndex("statement"));

					stList.add(st);

				} while (cursor.moveToNext());
			}
		}
		cursor.close();
		db.close();
		return stList;
	}


	public void removeStatement(String statement) {
		try {
			// SQLiteDatabase db = this.getWritableDatabase();
			// db.execSQL("delete from SYNCTABLE where statementIdNo="
			// + productid);
			// db.close();

			String[] args = { statement };
			getWritableDatabase().delete("SYNCTABLE", "statement=?", args);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public Statement getStatement() {

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(
				"select * from SYNCTABLE ORDER BY statementIdNo ASC LIMIT 1",
				null);
		Statement st = new Statement();

		if (cursor.getCount() != 0) {
			if (cursor.moveToFirst()) {

				st.statementId = cursor.getString(cursor
						.getColumnIndex("statementIdNo"));

				st.statement = cursor.getString(cursor
						.getColumnIndex("statement"));
			}
		}
		cursor.close();
		db.close();
		return st;
	}


	/**
	 * Storing user details in database
	 * */
	public boolean addLogin(User u) {
		SQLiteDatabase db = this.getWritableDatabase();
		boolean success = false;

		ContentValues values = new ContentValues();
		values.put(colUserFullName, u.getUserFullName()); // Email
		values.put(colUserEmail, u.getUserEmail()); // Email
		values.put(colUserPassword, u.getUserPassword()); // 
		values.put(colUserAddress, u.getUserAddress());
		values.put(colUserDob, u.getUserDob());		
		values.put(colCreateDate, todayDateTime);
		values.put(colUserType, u.getUserType());
		// Inserting Row
		if(db.insert(TABLE_LOGIN, null, values) > 0)
			{
				success = true;
			}
		db.close(); // Closing database connection
		return success;
	}

	

	/**
	 * Getting user login status
	 * return true if rows are there in table
	 * */
	public int getRowCount() {
		String countQuery = "SELECT  * FROM " + TABLE_LOGIN;
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		int rowCount = cursor.getCount();
		db.close();
		cursor.close();
		
		// return row count
		return rowCount;
	}
	
	/**
	 * Re crate database
	 * Delete all tables and create them again
	 * */
	public void resetTables(){
		SQLiteDatabase db = this.getWritableDatabase();
		// Delete All Rows
		db.delete(TABLE_LOGIN, null, null);
		db.close();
	}
	
	/**
	 * Function get Login status
	 * */
	public boolean isUserLoggedIn(){
		
		int count = this.getRowCount();
		if(count > 0){
			// user logged in
			return true;
		}
		return false;
	}
	
	/**
	 * Function to logout user
	 * Reset Database
	 * */
	public boolean logoutUser(){
		this.resetTables();
		return true;
	}
	
	/**
	 * Getting user data from database
	 * */
	public HashMap<String, String> getCustomerDetails(){
		HashMap<String,String> user = new HashMap<String,String>();
		String selectQuery = "SELECT  * FROM '" + TABLE_LOGIN + "'";
		 
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        
        if(cursor.getCount() > 0){
        	
        	user.put("userId", cursor.getString(0));
        	user.put("userFullName", cursor.getString(1));
        	user.put("userEmail", cursor.getString(2));
        	user.put("userPassword", cursor.getString(3));
        	user.put("userAddress", cursor.getString(4));
        	user.put("userDob", cursor.getString(5)); 	
        	user.put("updateDate", cursor.getString(6));
        	user.put("createDate", cursor.getString(7));
        	user.put("userType", cursor.getString(8));
        	
        }
        cursor.close();

        db.close();
		// return user

		return user;
	}
	
	public User getLogin(){
		//HashMap<String,String> user = new HashMap<String,String>();
		User u = new User();
		String selectQuery = "SELECT  * FROM '" + TABLE_LOGIN + "'";
		 
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        
        if(cursor.getCount() > 0){
        	u.setUserId(Integer.parseInt(cursor.getString(0)));
        	u.setUserFullName(cursor.getString(1));
        	u.setUserEmail(cursor.getString(2));
        	u.setUserAddress(cursor.getString(4));
        	u.setUserDob(cursor.getString(5));
        	u.setUpdateDate(cursor.getString(6));
        	u.setCreateDate(cursor.getString(7));
        	u.setUserType(cursor.getString(8));
        	Log.d("", "userId: " + cursor.getString(0));
        	Log.d("", "userFullName: " + cursor.getString(1));
        	Log.d("", "userEmail: " + cursor.getString(2));
        	Log.d("", "userType: " + cursor.getString(8));
        }
        cursor.close();

        db.close();
		// return user

		return u;
	}
	
	/**
	 * Storing user details in database
	 * */
	public boolean addReservation(Reservation r) {
		SQLiteDatabase db = this.getWritableDatabase();
		boolean success = false;

		ContentValues values = new ContentValues();
		values.put(colProductIdNo, r.getProductIdNo()); 
		values.put(colUserEmail, r.getUserEmail());
		values.put(colQuantity, r.getQuantity());
		values.put(colReserveCreateDate, todayPMDateTime);
		
		// Inserting Row
		if(db.insert(TABLE_RESERVATION, null, values) > 0)
			{
				success = true;
			}
		db.close(); // Closing database connection
		return success;
	}
	
	public boolean addReservationExample(SQLiteDatabase db) {
		boolean success = false;

		ContentValues values = new ContentValues();
		values.put(colProductIdNo, "1"); 
		values.put(colUserEmail, "customer");
		values.put(colQuantity, 10);
		values.put(colReserveCreateDate, todayPMDateTime);
		
		// Inserting Row
		if(db.insert(TABLE_RESERVATION, null, values) > 0)
			{
				success = true;
			}
		return success;
	}
	
	public ArrayList<Reservation> getReservationListByUserEmail(String userEmail) {
		String countQuery = "SELECT  * FROM " + TABLE_RESERVATION + " WHERE userEmail = '" + userEmail + 
				"'";
		Log.d("Query: ", countQuery);
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
        
        Reservation r = null;
        ArrayList<Reservation> rList = new ArrayList<Reservation>();
        //ArrayList<HashMap<String, String>> rList = new ArrayList<HashMap<String, String>>();
        //HashMap<String, String> r = new HashMap<String, String>();
        cursor.moveToFirst();
        if(cursor.moveToFirst()){
        	
            do{
            	r = new Reservation();
            	r.setReservationId(Integer.parseInt(cursor.getString(0)));
            	r.setUserEmail(cursor.getString(1));
            	r.setProductIdNo(cursor.getString(2));
            	r.setQuantity(Integer.parseInt(cursor.getString(3)));
            	r.setReserveCreateDate(cursor.getString(4));
            	rList.add(r);
            		
            	/*
        		r.put("reservationId", cursor.getString(0));
        		r.put("userId", cursor.getString(1));
        		r.put("productIdNo", cursor.getString(2));
        		r.put("quantity", cursor.getString(3));
        		r.put("reserveCreateDate", cursor.getString(4));
        		
        		rList.add(r);
        		*/
            }while(cursor.moveToNext());
        }

		
		db.close();
		cursor.close();
		
		// return row count
		return rList;
	}
}
